package com.xj.utils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
import com.xj.utils.PropertiesConfig;
import com.xj.utils.DatabaseInfo;

public class SynchronizeImpl implements Synchronize{
    private Connection source = null;
    private Connection target = null;
    private Statement sourceStmt = null;
    private Statement targetStmt = null;

    public SynchronizeImpl(Connection source, Connection target) {
        this.source = source;
        this.target = target;
        this.sourceStmt = getStatement(source);
        this.targetStmt = getStatement(target);
    }

    public static Statement getStatement(Connection con) {
        Statement stmt = null;
        try {
            stmt = con.createStatement();
        } catch (SQLException e) {
            System.out.println("获取statement失败:" + e.getMessage());
        }
        return stmt;
    }

    public List<String> getSourceDbs(){
        List<String> list = new ArrayList<String>();
        Statement statement = sourceStmt;
        try {
            ResultSet result = statement.executeQuery("SHOW DATABASES");
            while (result.next()) {
                String dbName = result.getString(1);
                if(!dbName.matches("information_schema|mysql|performance_schema|sys")){
                    list.add(dbName);
                }
            }
        } catch (SQLException e) {
            System.out.println("查询源dbs失败:" + e.getMessage());
        }
        return list;
    }

    public List<String> getFinalSourceDbs(){
        List<DatabaseInfo> databaseInfo = PropertiesConfig.getPropertiesConfigDBs();
        List<String> syncDatabases = databaseInfo.stream()
            .map(DatabaseInfo::getDbName)
            .collect(Collectors.toList());
        List<String> sourceDbs = getSourceDbs();
        List<String> filterSourceDbs = sourceDbs.stream()
                .filter(dbName -> syncDatabases.contains(dbName))
                .collect(Collectors.toList());
        if(filterSourceDbs.size() > 0){
            return filterSourceDbs;
        }
        return sourceDbs;
    }

    public List<String> getFinalSourceTbs(String dbName,List<String> tableList){
        List<DatabaseInfo> databaseInfo = PropertiesConfig.getPropertiesConfigDBs();
        List<String> tables = databaseInfo.stream().filter(dbInfo -> dbInfo.getDbName().equals(dbName)).findFirst().get().getTableList();
        if(tables != null && tables.size() > 0){
            return tableList.stream().filter(tableName -> tables.contains(tableName)).collect(Collectors.toList());
        }
        return tableList;
    }

    public void close(){
        try {
            if(sourceStmt != null){
                sourceStmt.close();
            }
            if(targetStmt != null){
                targetStmt.close();
            }
            if(source != null){
                source.close();
            }
            if(target != null){
                target.close();
            }
        } catch (SQLException e) {
            System.out.println("关闭数据库连接异常:" + e.getMessage());
        }
    }

    public void synchronizeDB() throws SQLException{
        for(int i = 0; i < getFinalSourceDbs().size(); i++){
            String dbName = getFinalSourceDbs().get(i);
            sourceStmt.execute("USE " + dbName);
            targetStmt.execute("CREATE DATABASE IF NOT EXISTS " + dbName + "_back");
            targetStmt.execute("USE " + dbName + "_back");
            System.out.println("===>开始同步数据库:" + dbName);
            List<String> allTableList = new ArrayList<>();
            ResultSet tables = sourceStmt.executeQuery("SHOW TABLES");
            while (tables.next()) {
                String tableName = tables.getString(1);
                allTableList.add(tableName);
            }
            tables.close();
            List<String> tableList = getFinalSourceTbs(dbName,allTableList);
            System.out.println("===>开始处理数据库:" + dbName + "中的表:" + tableList);
            for(String tableName : tableList){
                int rowCount = 0,tarRowCount = 0;boolean tarTableExists = false,isClearTarTab = true,isInsertTarTable = true;
                try (ResultSet countRs = sourceStmt.executeQuery("SELECT COUNT(*) FROM " + tableName)) {
                    countRs.next();
                    rowCount = countRs.getInt(1);
                    countRs.close();
                }
                try (ResultSet tarExists = targetStmt.executeQuery("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = '" + tableName + "'")) { 
                    tarExists.next();
                    if(tarExists.getInt(1) > 0){
                        try (ResultSet tarCountRs = targetStmt.executeQuery("SELECT COUNT(*) FROM " + tableName)) { 
                            tarCountRs.next();
                            tarRowCount = tarCountRs.getInt(1);
                            tarCountRs.close();
                        }
                        tarTableExists = true;
                    }
                    tarExists.close();
                }catch (SQLException e) {
                    System.out.println("Error: " + e.getMessage());
                }
                if(tarTableExists && (tarRowCount - rowCount > 20)){
                    isInsertTarTable = false;
                    isClearTarTab = false;
                }
                System.out.println("===>开始处理表:" + tableName + " 源表行数:" + rowCount  + " 目标表存在:" + tarTableExists + " 目标表行数:" + tarRowCount + " 是否插入目标表:" + isInsertTarTable + " 是否清空目标表:" + isClearTarTab);
                if (!tarTableExists) {
                    try (ResultSet createTableRs = sourceStmt.executeQuery("SHOW CREATE TABLE " + tableName)) {
                        if (createTableRs.next()) {
                            String createTableSql = createTableRs.getString(2);
                            targetStmt.execute("SET FOREIGN_KEY_CHECKS = 0");
                            targetStmt.execute(createTableSql);
                        }
                        createTableRs.close();
                    } catch (Exception e){
                        System.out.println("创建表:" + tableName + "异常:" + e.getMessage());
                    }
                } else {
                    if(isClearTarTab){
                        targetStmt.execute("SET FOREIGN_KEY_CHECKS = 0");
                        targetStmt.execute("DELETE FROM " + tableName + " WHERE 1");
                    }
                }
                if(isInsertTarTable){
                    ResultSet dataRs = sourceStmt.executeQuery("SELECT * FROM " + tableName);
                    ResultSetMetaData metaData = dataRs.getMetaData();
                    int columnCount = metaData.getColumnCount();
                    StringBuilder insertSql = new StringBuilder("INSERT INTO " + tableName + " VALUES (");
                    for (int c = 1; c <= columnCount; c++) {
                        insertSql.append("?");
                        if (c < columnCount) {
                            insertSql.append(",");
                        }
                    }
                    insertSql.append(")");
                    try (PreparedStatement pstmt = target.prepareStatement(insertSql.toString())) {
                        int batchSize = 0;
                        while (dataRs.next()) {
                            for (int k = 1; k <= columnCount; k++) {
                                pstmt.setObject(k, dataRs.getObject(k));
                            }
                            pstmt.addBatch();
                            batchSize++;
                            if (batchSize % 5 == 0) {
                                pstmt.executeBatch();
                                System.out.println("===>已处理表" + tableName + "的行数:" + batchSize);
                            }
                        }
                        if (batchSize % 5 != 0) {
                            pstmt.executeBatch();
                        }
                        System.out.println("===>表" + tableName + "处理完成, 共处理行数:" + batchSize);
                        dataRs.close();
                    } catch (SQLException e) {
                        System.out.println("===>目标表" + tableName + "数据插入异常" + e.getMessage());
                    }
                }
            }
        }
        close();
    }
}
